﻿Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data.Common

Module WeldLogDetail

    Function findWeldLogs(ByVal couponCode As String, ByVal barcode As String, ByVal weldInputDate As String, ByVal statusCode As String, ByVal weldLogDetailId As String, ByVal couponType As String, ByVal nonReplacedRjWeld As Boolean, ByVal welderId As String, ByVal projectId As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        ' , detail.BARCODE_OUT_1, detail.BARCODE_OUT_2, detail.BARCODE_IN_1
        Dim SQL As String = "SELECT detail.WELDLOG_DETAIL_ID, detail.BARCODE_REF, detail.BARCODE, detail.REJECT_BARCODE " & _
              "  , detail.SEQ, detail.COUPON_CODE, detail.COUPON_TYPE, detail.WELDER_USER_ID " & _
              "  , detail.WELD_DATE, detail.WELD_TIME, detail.QC_USER_ID, detail.QC_DATE " & _
              "  , detail.QC_TIME, detail.QC_STATUS, detail.REJECT_REASON, detail.REMARK " & _
              "  , detail.STATUS_CODE, detail.SYSTEM_TYPE_ID, detail.SYSTEM_TYPE_NAME, detail.HEAT_NUMBER_ID_1 " & _
              "  , detail.HEAT_NUMBER_NAME_1, detail.HEAT_NUMBER_ID_2, detail.HEAT_NUMBER_NAME_2, detail.JOINT_TYPE_CODE_1 " & _
              "  , detail.JOINT_TYPE_NAME_1, detail.JOINT_TYPE_CODE_2, detail.JOINT_TYPE_NAME_2, detail.ADD_BY " & _
              "  , detail.DATE_ADD, detail.TIME_ADD, detail.DATE_UPDATE, detail.TIME_UPDATE, detail.UPDATE_BY, detail.PDA_CODE " & _
              "  , welder.WELDER_NAME " & _
              "  , coupon.MATERIAL, coupon.SIZE, coupon.THICKNESS, coupon.IS_COAX, coupon.COAX, coupon.PIPE_TUBE, coupon.PROJECT_ID, coupon.WELD_MACHINE_ID, coupon.WELD_MACHINE_NAME " & _
              "     FROM eqam_weldlog_detail detail " & _
              " LEFT JOIN eqam_coupon coupon ON detail.COUPON_CODE = coupon.COUPON_CODE " & _
              " LEFT JOIN eqam_welder welder ON detail.WELDER_USER_ID = welder.WELDER_ID WHERE 1=1 "

        If (couponCode IsNot Nothing And couponCode <> "") Then
            SQL = SQL & " AND detail.COUPON_CODE = @COUPON_CODE"
            paramList.Add("@COUPON_CODE", couponCode)
        End If

        If (couponType IsNot Nothing And couponType <> "") Then
            SQL = SQL & " AND coupon.COUPON_TYPE = @COUPON_TYPE"
            paramList.Add("@COUPON_TYPE", couponType)
        End If

        If (weldInputDate IsNot Nothing And weldInputDate <> "") Then
            SQL = SQL & " AND detail.WELD_DATE = @WELD_DATE"
            paramList.Add("@WELD_DATE", weldInputDate)
        End If

        If (statusCode IsNot Nothing And statusCode <> "") Then
            SQL = SQL & " AND detail.STATUS_CODE = @STATUS_CODE"
            paramList.Add("@STATUS_CODE", statusCode)
        End If

        If (weldLogDetailId IsNot Nothing And weldLogDetailId <> "") Then
            SQL = SQL & " AND detail.WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"
            paramList.Add("@WELDLOG_DETAIL_ID", weldLogDetailId)
        End If

        If (welderId IsNot Nothing And welderId <> "") Then
            SQL = SQL & " AND detail.WELDER_USER_ID = @WELDER_USER_ID"
            paramList.Add("@WELDER_USER_ID", welderId)
        End If

        If (projectId IsNot Nothing And projectId <> "") Then
            SQL = SQL & " AND coupon.PROJECT_ID = @PROJECT_ID"
            paramList.Add("@PROJECT_ID", projectId)
        End If

        If (barcode IsNot Nothing And barcode <> "") Then
            SQL = SQL & " AND (detail.BARCODE = @BARCODE OR detail.BARCODE_REF = @BARCODE_REF)"
            paramList.Add("@BARCODE", barcode)
            paramList.Add("@BARCODE_REF", barcode)
        End If

        If (nonReplacedRjWeld = True) Then
            SQL = SQL & " AND (detail.REPLACEMENT_ID = '' OR detail.REPLACEMENT_ID IS NULL)"
        End If

        SQL = SQL & " ORDER BY SEQ "

        Return LoadData(SQL, paramList)

    End Function

    Function findAcceptRejectWeldLogs(ByVal barcode As String, ByVal updateDate As String, ByVal statusCode As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT detail.WELDLOG_DETAIL_ID, detail.BARCODE_REF, detail.BARCODE " & _
              "  , detail.SEQ, detail.COUPON_CODE, detail.COUPON_TYPE, detail.WELDER_USER_ID " & _
              "  , detail.WELD_DATE, detail.WELD_TIME, detail.QC_USER_ID, detail.QC_DATE " & _
              "  , detail.QC_TIME, detail.QC_STATUS, detail.REJECT_REASON, detail.REMARK " & _
              "  , detail.STATUS_CODE, detail.SYSTEM_TYPE_ID, detail.SYSTEM_TYPE_NAME, detail.HEAT_NUMBER_ID_1 " & _
              "  , detail.HEAT_NUMBER_NAME_1, detail.HEAT_NUMBER_ID_2, detail.HEAT_NUMBER_NAME_2, detail.JOINT_TYPE_CODE_1 " & _
              "  , detail.JOINT_TYPE_NAME_1, detail.JOINT_TYPE_CODE_2, detail.JOINT_TYPE_NAME_2, detail.ADD_BY " & _
              "  , detail.DATE_ADD, detail.TIME_ADD, detail.DATE_UPDATE, detail.TIME_UPDATE, detail.UPDATE_BY, detail.PDA_CODE " & _
              "  , coupon.MATERIAL, coupon.SIZE, coupon.THICKNESS, coupon.IS_COAX, coupon.COAX, coupon.PIPE_TUBE " & _
              "     FROM eqam_weldlog_detail detail " & _
              " LEFT JOIN " & _
              "     ( SELECT COUPON_CODE, MATERIAL, SIZE, THICKNESS, IS_COAX, COAX, PIPE_TUBE FROM eqam_coupon GROUP BY COUPON_CODE, MATERIAL, SIZE, THICKNESS, IS_COAX, COAX, PIPE_TUBE) coupon ON detail.COUPON_CODE = coupon.COUPON_CODE " & _
              "     WHERE 1=1 "

        If (updateDate IsNot Nothing And updateDate <> "") Then
            SQL = SQL & " AND detail.DATE_UPDATE = @DATE_UPDATE"
            paramList.Add("@DATE_UPDATE", updateDate)
        End If

        If (statusCode IsNot Nothing And statusCode <> "") Then
            SQL = SQL & " AND detail.STATUS_CODE = @STATUS_CODE"
            paramList.Add("@STATUS_CODE", statusCode)
        Else
            SQL = SQL & " AND detail.STATUS_CODE IN ('" & WELDLOG_STATUS_ACCEPT & "','" & WELDLOG_STATUS_REJECTED & "')"
        End If

        If (barcode IsNot Nothing And barcode <> "") Then
            SQL = SQL & " AND detail.BARCODE = @BARCODE"
            paramList.Add("@BARCODE", barcode)
        End If

        SQL = SQL & " ORDER BY DATE_UPDATE, TIME_UPDATE desc "

        Return LoadData(SQL, paramList)

    End Function

    Public Function checkBarcodeDuplicationForWeldlogDetail(ByVal barcode As String, ByVal ignoreWeldlogDetailId As String) As Boolean
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim result As Boolean = False

        Dim SQL As String = "SELECT BARCODE FROM eqam_weldlog_detail WHERE (BARCODE = @BARCODE OR BARCODE_REF = @BARCODE_REF) "

        paramList.Add("@BARCODE", barcode)
        paramList.Add("@BARCODE_REF", barcode)

        If (ignoreWeldlogDetailId IsNot Nothing And ignoreWeldlogDetailId <> "") Then
            SQL = SQL & "  AND WELDLOG_DETAIL_ID <> @WELDLOG_DETAIL_ID"
            paramList.Add("@WELDLOG_DETAIL_ID", ignoreWeldlogDetailId)
        End If

        dtResult = LoadData(SQL, paramList)
        If dtResult.Rows.Count > 0 Then
            result = True
        End If

        Return result
    End Function

    Public Function getWeldLogDetailMaxSeq(ByVal couponCode As String) As Integer
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim SQL As String = ""
        Dim seq As Integer = 1
        
        Dim dateAdd As String = PdaUtils.getCurrentDate        
        SQL = "SELECT max(SEQ) AS _SEQ FROM eqam_weldlog_detail WHERE COUPON_CODE = @COUPON_CODE"

        paramList.Add("@COUPON_CODE", couponCode)

        dtResult = LoadData(SQL, paramList)
        If dtResult.Rows.Count > 0 Then
            Dim dataRow As DataRow = dtResult.Rows(0)
            If (IsDBNull(dataRow.Item("_SEQ")) = True) Then
                seq = 1
            Else
                seq = dataRow.Item("_SEQ") + 1
            End If
        End If

        Return seq
    End Function

    Public Function getWeldLogDetail(ByVal weldlogDetailId As String) As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()
        Dim SQL As String = ""
        Dim seq As Integer = 1

        Dim dateAdd As String = PdaUtils.getCurrentDate
        SQL = "SELECT * FROM eqam_weldlog_detail WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

        paramList.Add("@WELDLOG_DETAIL_ID", weldlogDetailId)

        Return LoadData(SQL, paramList)
    End Function

    Public Function doCreateWeldLog(ByVal userId As String, ByVal barcodeO1 As String, ByVal barcodeI As String, ByVal systemId As String, ByVal systemName As String, ByVal heatId1 As String, ByVal heatName1 As String, ByVal heatId2 As String, ByVal heatName2 As String, ByVal jointTypeCode1 As String, ByVal jointTypeName1 As String, ByVal jointTypeCode2 As String, ByVal jointTypeName2 As String, ByVal seq As Integer, ByVal areaId As String, ByVal areaName As String) As Boolean
        Dim paramList As New Hashtable()
        Dim sql As String = ""
        Dim currentDate As Decimal = PdaUtils.getCurrentDate
        Dim currentTime As Decimal = PdaUtils.getCurrentTime

        '''''''''''''''''   check duplicate seq   '''''''''''''''''''''''            
        Dim dtResult As New DataTable
        sql = "SELECT WELDLOG_DETAIL_ID, SEQ" & _
              "     FROM eqam_weldlog_detail WHERE COUPON_CODE = @COUPON_CODE AND  SEQ >= @SEQ_SEQ AND COUPON_TYPE = @COUPON_TYPE ORDER BY SEQ"
        paramList.Add("@COUPON_CODE", _COUPON_CODE)
        paramList.Add("@SEQ_SEQ", seq)
        paramList.Add("@COUPON_TYPE", _COUPON_TYPE)

        dtResult = LoadData(sql, paramList)

        Dim duplicateSeqId As Integer
        Dim lastSeqNo As Integer = seq
        If dtResult.Rows.Count > 0 Then
            For Each dataRow As DataRow In dtResult.Rows
                lastSeqNo = lastSeqNo + 1
                duplicateSeqId = dataRow.Item("WELDLOG_DETAIL_ID")

                sql = "UPDATE eqam_weldlog_detail SET SEQ = @NEW_SEQ, DATE_UPDATE = @NEW_DATE_UPDATE, TIME_UPDATE = @NEW_TIME_UPDATE" & _
                                  " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

                paramList = New Hashtable()
                paramList.Add("@NEW_SEQ", lastSeqNo)
                paramList.Add("@WELDLOG_DETAIL_ID", duplicateSeqId)
                paramList.Add("@NEW_DATE_UPDATE", currentDate)
                paramList.Add("@NEW_TIME_UPDATE", currentTime)

                ExecuteSQL(sql, paramList)
            Next
        End If

        sql = "INSERT INTO eqam_weldlog_detail(ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY, STATUS_CODE" & _
                          " ,BARCODE_REF, BARCODE, COUPON_CODE, COUPON_TYPE" & _
                          " ,WELDER_USER_ID, WELD_DATE, WELD_TIME, SYSTEM_TYPE_ID, SYSTEM_TYPE_NAME" & _
                          " ,JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2" & _
                          " ,HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2, HEAT_NUMBER_NAME_2, SEQ, PDA_CODE, ZONE_ID, ZONE)" & _
                          " VALUES (@ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY, @STATUS_CODE" & _
                          " ,@BARCODE_OUT1, @BARCODE_IN1,@COUPON_CODE, @COUPON_TYPE " & _
                          " ,@WELDER_USER_ID, @WELD_DATE, @WELD_TIME, @SYSTEM_TYPE_ID, @SYSTEM_TYPE_NAME" & _
                          " ,@JOINT_TYPE_CODE_1, @JOINT_TYPE_NAME_1, @JOINT_TYPE_CODE_2, @JOINT_TYPE_NAME_2" & _
                          " ,@HEAT_NUMBER_ID_1, @HEAT_NUMBER_NAME_1, @HEAT_NUMBER_ID_2, @HEAT_NUMBER_NAME_2, @SEQ, @PDA_CODE, @ZONE_ID, @ZONE)"

        'first line
        paramList = New Hashtable()
        paramList.Add("@ADD_BY", _USER_ID)
        paramList.Add("@DATE_ADD", currentDate)
        paramList.Add("@TIME_ADD", currentTime)
        paramList.Add("@DATE_UPDATE", currentDate)
        paramList.Add("@TIME_UPDATE", currentTime)
        paramList.Add("@UPDATE_BY", _USER_ID)
        paramList.Add("@STATUS_CODE", Globals.WELDLOG_STATUS_PENDING)
        'sec line
        paramList.Add("@BARCODE_OUT1", barcodeO1)
        paramList.Add("@BARCODE_IN1", barcodeI)
        paramList.Add("@COUPON_CODE", _COUPON_CODE)
        paramList.Add("@COUPON_TYPE", _COUPON_TYPE)
        'third line
        paramList.Add("@WELDER_USER_ID", _USER_ID)
        paramList.Add("@WELD_DATE", currentDate)
        paramList.Add("@WELD_TIME", currentTime)
        paramList.Add("@SYSTEM_TYPE_ID", systemId)
        paramList.Add("@SYSTEM_TYPE_NAME", systemName)

        paramList.Add("@JOINT_TYPE_CODE_1", jointTypeCode1)
        paramList.Add("@JOINT_TYPE_NAME_1", jointTypeName1)
        paramList.Add("@JOINT_TYPE_CODE_2", jointTypeCode2)
        paramList.Add("@JOINT_TYPE_NAME_2", jointTypeName2)

        paramList.Add("@HEAT_NUMBER_ID_1", heatId1)
        paramList.Add("@HEAT_NUMBER_NAME_1", heatName1)
        paramList.Add("@HEAT_NUMBER_ID_2", heatId2)
        paramList.Add("@HEAT_NUMBER_NAME_2", heatName2)
        paramList.Add("@SEQ", seq)
        paramList.Add("@PDA_CODE", _PDA_CODE)

        paramList.Add("@ZONE_ID", areaId)
        paramList.Add("@ZONE", areaName)

        Return ExecuteSQL(sql, paramList)
    End Function

    Public Function updateWeldLog(ByVal userId As String, ByVal barcodeRef As String, ByVal barcode As String, ByVal systemId As String, ByVal systemName As String, ByVal heatId1 As String, ByVal heatName1 As String, ByVal heatId2 As String, ByVal heatName2 As String, ByVal jointTypeCode1 As String, ByVal jointTypeName1 As String, ByVal jointTypeCode2 As String, ByVal jointTypeName2 As String, ByVal seq As Integer, ByVal weldLogDetailId As String, ByVal areaId As String, ByVal areaName As String) As Boolean
        Dim paramList As New Hashtable()
        Dim currentDate As Decimal = PdaUtils.getCurrentDate
        Dim currentTime As Decimal = PdaUtils.getCurrentTime

        '''''''''''''''''   check duplicate seq   '''''''''''''''''''''''            
        Dim dtResult As New DataTable
        Dim sql As String = "SELECT WELDLOG_DETAIL_ID, SEQ" & _
              "     FROM eqam_weldlog_detail WHERE COUPON_CODE = @COUPON_CODE AND  SEQ >= @SEQ_SEQ AND COUPON_TYPE = @COUPON_TYPE AND WELDLOG_DETAIL_ID <> @WELDLOG_DETAIL_ID ORDER BY SEQ"
        paramList.Add("@COUPON_CODE", _COUPON_CODE)
        paramList.Add("@SEQ_SEQ", seq)
        paramList.Add("@COUPON_TYPE", _COUPON_TYPE)
        paramList.Add("@WELDLOG_DETAIL_ID", weldLogDetailId)

        dtResult = LoadData(sql, paramList)

        Dim duplicateSeqId As Integer
        Dim lastSeqNo As Integer = seq
        If dtResult.Rows.Count > 0 Then
            For Each dataRow As DataRow In dtResult.Rows
                lastSeqNo = lastSeqNo + 1
                duplicateSeqId = dataRow.Item("WELDLOG_DETAIL_ID")

                sql = "UPDATE eqam_weldlog_detail SET SEQ = @NEW_SEQ, DATE_UPDATE = @NEW_DATE_UPDATE, TIME_UPDATE = @NEW_TIME_UPDATE" & _
                                  " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

                paramList = New Hashtable()
                paramList.Add("@NEW_SEQ", lastSeqNo)
                paramList.Add("@WELDLOG_DETAIL_ID", duplicateSeqId)
                paramList.Add("@NEW_DATE_UPDATE", currentDate)
                paramList.Add("@NEW_TIME_UPDATE", currentTime)

                ExecuteSQL(sql, paramList)
            Next
        End If
        ''''''''''''''''' ~ end check duplicate seq end ~  ''''''''''''''''''''''' 
        paramList = New Hashtable()
        sql = "UPDATE eqam_weldlog_detail SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                          " ,BARCODE_REF = @BARCODE_REF, BARCODE = @BARCODE, COUPON_CODE = @COUPON_CODE, COUPON_TYPE = @COUPON_TYPE" & _
                          " ,SYSTEM_TYPE_ID = @SYSTEM_TYPE_ID, SYSTEM_TYPE_NAME = @SYSTEM_TYPE_NAME" & _
                          " ,JOINT_TYPE_CODE_1 = @JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1 = @JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2 = @JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2 = @JOINT_TYPE_NAME_2" & _
                          " ,HEAT_NUMBER_ID_1 = @HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1 = @HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2 = @HEAT_NUMBER_ID_2, HEAT_NUMBER_NAME_2 = @HEAT_NUMBER_NAME_2, SEQ = @SEQ, ZONE_ID = @ZONE_ID, ZONE = @ZONE" & _
                          " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

        'first line                
        paramList.Add("@DATE_UPDATE", currentDate)
        paramList.Add("@TIME_UPDATE", currentTime)
        paramList.Add("@UPDATE_BY", _USER_ID)

        'sec line
        paramList.Add("@BARCODE_REF", barcodeRef)
        paramList.Add("@BARCODE", barcode)
        paramList.Add("@COUPON_CODE", _COUPON_CODE)
        paramList.Add("@COUPON_TYPE", _COUPON_TYPE)
        'third line
        paramList.Add("@SYSTEM_TYPE_ID", systemId)
        paramList.Add("@SYSTEM_TYPE_NAME", systemName)

        paramList.Add("@JOINT_TYPE_CODE_1", jointTypeCode1)
        paramList.Add("@JOINT_TYPE_NAME_1", jointTypeName1)
        paramList.Add("@JOINT_TYPE_CODE_2", jointTypeCode2)
        paramList.Add("@JOINT_TYPE_NAME_2", jointTypeName2)

        paramList.Add("@HEAT_NUMBER_ID_1", heatId1)
        paramList.Add("@HEAT_NUMBER_NAME_1", heatName1)
        paramList.Add("@HEAT_NUMBER_ID_2", heatId2)
        paramList.Add("@HEAT_NUMBER_NAME_2", heatName2)
        paramList.Add("@SEQ", seq)
        paramList.Add("@WELDLOG_DETAIL_ID", weldLogDetailId)

        paramList.Add("@ZONE_ID", areaId)
        paramList.Add("@ZONE", areaName)

        Return ExecuteSQL(sql, paramList)
    End Function

    Public Function acceptWeldLog(ByVal systemId As String, ByVal systemName As String, ByVal heatId1 As String, ByVal heatName1 As String, ByVal heatId2 As String, ByVal heatName2 As String, ByVal jointTypeCode1 As String, ByVal jointTypeName1 As String, ByVal jointTypeCode2 As String, ByVal jointTypeName2 As String, ByVal seq As Integer, ByVal weldLogDetailId As String) As Boolean
        Dim paramList As New Hashtable()

        Dim sql As String = "UPDATE eqam_weldlog_detail SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                          " ,STATUS_CODE = @STATUS_CODE, SYSTEM_TYPE_ID = @SYSTEM_TYPE_ID, SYSTEM_TYPE_NAME = @SYSTEM_TYPE_NAME" & _
                          " ,JOINT_TYPE_CODE_1 = @JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1 = @JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2 = @JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2 = @JOINT_TYPE_NAME_2" & _
                          " ,HEAT_NUMBER_ID_1 = @HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1 = @HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2 = @HEAT_NUMBER_ID_2, HEAT_NUMBER_NAME_2 = @HEAT_NUMBER_NAME_2, SEQ = @SEQ" & _
                          " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"
        Dim currentDate As Decimal = PdaUtils.getCurrentDate
        Dim currentTime As Decimal = PdaUtils.getCurrentTime

        'first line                
        paramList.Add("@DATE_UPDATE", currentDate)
        paramList.Add("@TIME_UPDATE", currentTime)
        paramList.Add("@UPDATE_BY", _USER_ID)

        'third line
        paramList.Add("@STATUS_CODE", WELDLOG_STATUS_ACCEPT)
        paramList.Add("@SYSTEM_TYPE_ID", systemId)
        paramList.Add("@SYSTEM_TYPE_NAME", systemName)

        paramList.Add("@JOINT_TYPE_CODE_1", jointTypeCode1)
        paramList.Add("@JOINT_TYPE_NAME_1", jointTypeName1)
        paramList.Add("@JOINT_TYPE_CODE_2", jointTypeCode2)
        paramList.Add("@JOINT_TYPE_NAME_2", jointTypeName2)

        paramList.Add("@HEAT_NUMBER_ID_1", heatId1)
        paramList.Add("@HEAT_NUMBER_NAME_1", heatName1)
        paramList.Add("@HEAT_NUMBER_ID_2", heatId2)
        paramList.Add("@HEAT_NUMBER_NAME_2", heatName2)
        paramList.Add("@SEQ", seq)
        paramList.Add("@WELDLOG_DETAIL_ID", weldLogDetailId)

        _SAVE_SUCCESSFUL_MESSAGE = "Update Successfully."
        Return ExecuteSQL(sql, paramList)
    End Function

    Public Function deleteWeldLog(ByVal ids As ArrayList) As Boolean
        Dim paramList As New Hashtable()

        Dim SQL As String = "DELETE FROM eqam_weldlog_detail WHERE 1=1"
        Dim WHERE_STATEMENT = ""

        If (ids IsNot Nothing) Then
            Dim seq As Integer
            Dim idx As Integer = 0
            If (ids.Count > 0) Then
                WHERE_STATEMENT = WHERE_STATEMENT & " AND ("
                For Each seq In ids
                    If (idx > 0) Then
                        WHERE_STATEMENT = WHERE_STATEMENT & " OR "
                    End If
                    WHERE_STATEMENT = WHERE_STATEMENT & " WELDLOG_DETAIL_ID = " & seq
                    idx = idx + 1
                    Dim dtResult As New DataTable
                    paramList = New Hashtable()
                    Dim lstView As New ListViewItem
                    Dim weldlogBarcode As String

                    dtResult = getWeldLogDetail(seq)

                    If dtResult.Rows.Count > 0 Then
                        ''''' retrieve barcode
                        Dim dataRow As DataRow = dtResult.Rows(0)
                        weldlogBarcode = dataRow.Item("BARCODE")

                        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                        ' update rejected weld to normal once replacement weld been deleted 
                        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

                        Dim UPDATE_REPLACEMENT_SQL As String = "UPDATE eqam_weldlog_detail " & _
                            "SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                            " ,REPLACEMENT_ID = NULL, REPLACE_BARCODE = NULL WHERE REPLACE_BARCODE = @REPLACE_BARCODE"

                        Dim currentDate As Decimal = PdaUtils.getCurrentDate
                        Dim currentTime As Decimal = PdaUtils.getCurrentTime
                        paramList = New Hashtable()
                        paramList.Add("@DATE_UPDATE", currentDate)
                        paramList.Add("@TIME_UPDATE", currentTime)
                        paramList.Add("@UPDATE_BY", _USER_ID)
                        paramList.Add("@REPLACE_BARCODE", weldlogBarcode)

                        ExecuteSQL(UPDATE_REPLACEMENT_SQL, paramList)
                    End If
                Next
                WHERE_STATEMENT = WHERE_STATEMENT & ")"
            End If
        End If

        paramList = New Hashtable()
        Return ExecuteSQL(SQL & WHERE_STATEMENT, paramList)
    End Function

    Public Function updateWeldStatus(ByVal remark As String, ByVal rejectReason As String, ByVal status As String, ByVal weldId As String) As Boolean
        Dim paramList As New Hashtable()
        Dim SQL As String = "UPDATE eqam_weldlog_detail SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY, STATUS_CODE = @STATUS_CODE, REMARK = @REMARKS, REJECT_REASON = @REJECT_REASON" & _
                          " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime

        paramList.Add("@DATE_UPDATE", currentDate)
        paramList.Add("@TIME_UPDATE", currentTime)
        paramList.Add("@UPDATE_BY", _USER_ID)
        paramList.Add("@STATUS_CODE", status)
        paramList.Add("@REMARKS", remark)
        paramList.Add("@REJECT_REASON", rejectReason)
        paramList.Add("@WELDLOG_DETAIL_ID", weldId)

        _SAVE_SUCCESSFUL_MESSAGE = "Update Successfully."
        Return ExecuteSQL(SQL, paramList)
    End Function

    Public Function saveRejectedWeldNo(ByVal projectId As String, ByVal rejectedWeldId As String, ByVal rejectedBarcode As String, ByVal replacementBarcode As String, ByVal couponCode As String, ByVal seq As Integer) As Boolean
        Dim conn As New SqlCeConnection()
        Dim cmd As SqlCeCommand
        Dim sqlCeTransaction As SqlCeTransaction
        Dim SQL As String = ""
        Dim success As Boolean

        Dim dtResult As New DataTable

        Dim currentDate As Decimal = PdaUtils.getCurrentDate
        Dim currentTime As Decimal = PdaUtils.getCurrentTime

        Try
            conn = New SqlCeConnection(sCon)

            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            Dim paramList As New Hashtable()

            'dtResult = findWeldLogs(Nothing, Nothing, Nothing, Nothing, rejectedWeldId, Nothing, False, Nothing, _PROJECT_ID)

            'Dim rjBarcodeOut1 As String = ""
            'Dim rjBarcodeOut2 As String = ""
            'Dim rjBarcodeIn1 As String = ""
            'Dim rjWlPdaCode As String = ""

            'If dtResult.Rows.Count > 0 Then
            'For Each dataRow As DataRow In dtResult.Rows
            'If (DataRow.Item("BARCODE_REF") <> Nothing) Then
            'rjBarcodeOut1 = DataRow.Item("BARCODE_REF")
            'End If

            'If (DataRow.Item("BARCODE") <> Nothing) Then
            'rjBarcodeIn1 = DataRow.Item("BARCODE")
            'End If
            'If (DataRow.Item("PDA_CODE") <> Nothing) Then
            'rjWlPdaCode = DataRow.Item("PDA_CODE")
            'End If
            'Next
            'End If

            '''''''''''''''''   check duplicate seq   '''''''''''''''''''''''                        
            SQL = "SELECT WELDLOG_DETAIL_ID, SEQ" & _
                  "     FROM eqam_weldlog_detail WHERE COUPON_CODE = @COUPON_CODE AND  SEQ >= @SEQ_SEQ AND COUPON_TYPE = @COUPON_TYPE ORDER BY SEQ"
            paramList.Add("@COUPON_CODE", couponCode)
            paramList.Add("@SEQ_SEQ", seq)
            paramList.Add("@COUPON_TYPE", "I")

            dtResult = LoadData(SQL, paramList)

            Dim duplicateSeqId As Integer
            Dim lastSeqNo As Integer = seq
            If dtResult.Rows.Count > 0 Then
                For Each dataRow As DataRow In dtResult.Rows
                    lastSeqNo = lastSeqNo + 1
                    duplicateSeqId = dataRow.Item("WELDLOG_DETAIL_ID")

                    SQL = "UPDATE eqam_weldlog_detail SET SEQ = @NEW_SEQ, DATE_UPDATE = @NEW_DATE_UPDATE, TIME_UPDATE = @NEW_TIME_UPDATE" & _
                                      " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

                    paramList = New Hashtable()
                    paramList.Add("@NEW_SEQ", lastSeqNo)
                    paramList.Add("@WELDLOG_DETAIL_ID", duplicateSeqId)
                    paramList.Add("@NEW_DATE_UPDATE", currentDate)
                    paramList.Add("@NEW_TIME_UPDATE", currentTime)

                    ExecuteSQL(SQL, paramList)
                Next
            End If

            ''''''''''''''''' START DUPLICATE WELD LOG '''''''''''''''''
            sqlCeTransaction = conn.BeginTransaction
            cmd = conn.CreateCommand
            cmd.CommandText = "INSERT INTO eqam_weldlog_detail" & _
                          "(BARCODE_REF, BARCODE, SEQ, COUPON_CODE, COUPON_TYPE, WELDER_USER_ID, WELD_DATE, WELD_TIME,  " & _
                          "QC_USER_ID, QC_DATE, QC_TIME, QC_STATUS, REJECT_REASON, REPLACEMENT_ID, REJECT_BARCODE, REMARK,  " & _
                                                  "STATUS_CODE, SYSTEM_TYPE_ID, SYSTEM_TYPE_NAME, HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2,  " & _
                                                  "HEAT_NUMBER_NAME_2, JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2, PDA_CODE, ADD_BY,  " & _
                                                  "DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY, ZONE_ID, ZONE) " & _
                            "SELECT BARCODE_REF, BARCODE, SEQ, COUPON_CODE, COUPON_TYPE, WELDER_USER_ID, WELD_DATE, WELD_TIME,  " & _
                                                  "QC_USER_ID, QC_DATE, QC_TIME, QC_STATUS, REJECT_REASON, REPLACEMENT_ID, REJECT_BARCODE, REMARK,  " & _
                                                  "STATUS_CODE, SYSTEM_TYPE_ID, SYSTEM_TYPE_NAME, HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2,  " & _
                                                  "HEAT_NUMBER_NAME_2, JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2, PDA_CODE, ADD_BY,  " & _
                                    "DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY, ZONE_ID, ZONE " & _
                            "FROM         eqam_weldlog_detail AS eqam_weldlog_detail_1 " & _
                            "WHERE     (WELDLOG_DETAIL_ID = " & rejectedWeldId & ")"
            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()

            cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
            Dim newWeldId As Integer = cmd.ExecuteScalar

            ''''''''''''''''' END DUPLICATE WELD LOG '''''''''''''''''

            cmd = conn.CreateCommand
            cmd.CommandText = "INSERT INTO eqam_weld_replacement(ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY" & _
                                ", PROJECT_ID, REJECT_BARCODE" & _
                                ", REPLACE_BARCODE)" & _
                              " VALUES (@ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY" & _
                                ", @PROJECT_ID, @REJECT_BARCODE" & _
                                ", @REPLACE_BARCODE)"

            'first line        
            cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
            cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
            cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            cmd.Parameters.AddWithValue("@REJECT_BARCODE", rejectedBarcode)
            cmd.Parameters.AddWithValue("@REPLACE_BARCODE", replacementBarcode)
            cmd.Parameters.AddWithValue("@PDA_CODE", _PDA_CODE)

            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
            Dim replacementId As Integer = cmd.ExecuteScalar

            ''''''''''''''''' UPDATE WELD LOG '''''''''''''''''
            cmd = conn.CreateCommand
            cmd.CommandText = "UPDATE eqam_weldlog_detail " & _
                "SET BARCODE = @BARCODE, COUPON_CODE = @COUPON_CODE, REPLACEMENT_ID = @REPLACEMENT_ID, REJECT_BARCODE = @REJECT_BARCODE, SEQ = @SEQ" & _
                "     , STATUS_CODE = @STATUS_CODE, DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY, ADD_BY = @ADD_BY, DATE_ADD = @DATE_ADD, TIME_ADD = @TIME_ADD " & _
                " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

            cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
            cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
            cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)
            cmd.Parameters.AddWithValue("@STATUS_CODE", Globals.WELDLOG_STATUS_PENDING)

            cmd.Parameters.AddWithValue("@BARCODE", replacementBarcode)
            cmd.Parameters.AddWithValue("@COUPON_CODE", couponCode)
            cmd.Parameters.AddWithValue("@REPLACEMENT_ID", replacementId)
            cmd.Parameters.AddWithValue("@REJECT_BARCODE", rejectedBarcode)
            cmd.Parameters.AddWithValue("@SEQ", seq)

            cmd.Parameters.AddWithValue("@WELDLOG_DETAIL_ID", newWeldId)

            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()

            cmd = conn.CreateCommand
            SQL = "UPDATE eqam_weldlog_detail " & _
                "SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                " ,REPLACEMENT_ID = @REPLACEMENT_ID, REPLACE_BARCODE = @REPLACE_BARCODE" & _
                " WHERE WELDLOG_DETAIL_ID = @WELDLOG_DETAIL_ID"

            cmd.CommandText = SQL
            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

            cmd.Parameters.AddWithValue("@REPLACEMENT_ID", replacementId)
            cmd.Parameters.AddWithValue("@REPLACE_BARCODE", replacementBarcode)

            cmd.Parameters.AddWithValue("@WELDLOG_DETAIL_ID", rejectedWeldId)

            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()

            ''''''''''''''''' END UPDATE WELD LOG ''''''''''''''''
            sqlCeTransaction.Commit()
            success = True
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, sysname & " - ex")
            MsgBox("LOCAL CONNECTION REPLACE WELD: " & vbCrLf & ex.Message, MsgBoxStyle.Information, sysname & " - ExecuteSQL")
            sqlCeTransaction.Rollback()
            success = False
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try
        Return success
    End Function

    Function findAcceptRejectWeldlogUpdateDateByGroup() As DataTable
        Dim dtResult As New DataTable
        Dim paramList As New Hashtable()

        Dim SQL As String = "SELECT DATE_UPDATE FROM eqam_weldlog_detail " & _
                " WHERE STATUS_CODE IN ('" & WELDLOG_STATUS_ACCEPT & "','" & WELDLOG_STATUS_REJECTED & "')" & _
                " GROUP BY DATE_UPDATE ORDER BY DATE_UPDATE DESC"

        Return LoadData(SQL, paramList)
    End Function
End Module
